# encoding=utf-8

import xlrd
import re
import sys
import types
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from datetime import datetime,date,timedelta
'''
reload(sys)
sys.setdefaultencoding('utf8')
'''
mail_host = "spam.avita.com.tw"  # 设置服务器
mail_user = "tao_lu"  # 用户名
mail_pass = "P2ss.w0rd"  # 口令

sender = 'tao_lu@avita.com.tw'




def getDeviceName(table):
    ncols = table.ncols
    for col in range(ncols):

        for cell in table.col_values(col):

            isString = isinstance(cell, basestring)

            if isString:
                #cellString = cell.replace(' ', '')

                deviceNamePattern = re.compile(u'\u8bbe\u5907\u540d\u79f0(\:|\uff1a)(.{1,9})')
                deviceName = deviceNamePattern.search(cell)

                if deviceName:
                    return  deviceName.group(2)

def getCheckManColIndex(table):

    ncols = table.ncols
    checkManColIndex = 0
    for col in range(ncols):

        for cell in table.col_values(col):
            checkManColIndex += 1

            isString = isinstance(cell, basestring)

            if isString:
                cellString = cell.replace(' ', '')
                checkManPattern = re.compile(u'\u4fdd\u517b\u4eba')

                checkMan = checkManPattern.search(cellString)

                if checkMan:
                    return checkManColIndex - 1


def getCheckYeadMonth(table):
    ncols = table.ncols
    for col in range(ncols):

        for cell in table.col_values(col):
            isString = isinstance(cell, basestring)

            if isString:
                cellString = cell.replace(' ', '')

                checkYeadMonthPattern = re.compile(u'(\d+)\u5e74(\d+)\u6708')
                checkYeadMonth = checkYeadMonthPattern.search(cellString)

                if checkYeadMonth:
                    return (checkYeadMonth.group(1),checkYeadMonth.group(2))


def getCheckMan(CheckRowIndex,table):
    if checkManColIndex:
        checkManTuple = []
        for i in range(2,33):
            cellCheckMan = table.col(i)[CheckRowIndex].value
            checkManTuple.append(cellCheckMan)
        return checkManTuple

def getConfirmor(CheckRowIndex,table):
    if checkManColIndex:
        checkManTuple = []
        for i in range(2,33):
            cellCheckMan = table.col(i)[CheckRowIndex].value
            checkManTuple.append(cellCheckMan)
        return checkManTuple


def getEmailAddress(text):
    emailRegex = r"[-_\w\.]{0,64}@[-\w]{1,63}\.*[-\w]{1,63}\.*[-\w]*"
    emailAddressPattern = re.compile(emailRegex)
    emailList = []
    if type(text) is types.ListType:

        for item in text:


            emailAddress = emailAddressPattern.findall(item)
            emailList.append(emailAddress)

    return emailList

#xlrd.Book.encoding = "gbk"
workbook = xlrd.open_workbook(r'TPM20170915.xls')

sheets = workbook.sheets()

today = date.today()
delta = timedelta(days=3)
threeDay = today + delta


for table in sheets:
    receivers = []
    checkYeadMonth = getCheckYeadMonth(table)

    if not checkYeadMonth:
        continue

    tableRecordDate = date(int(checkYeadMonth[0]), int(checkYeadMonth[1]), 1)

    if threeDay.month - tableRecordDate.month != 0:
       continue

    checkManColIndex = 0

    deviceName = getDeviceName(table)

    checkManColIndex = getCheckManColIndex(table)
    confirmorColIndex = checkManColIndex + 1

    checkManList = getCheckMan(checkManColIndex,table)
    confirmorList = getConfirmor(confirmorColIndex,table)

    emailListCheckMan = getEmailAddress(checkManList)
    emailListConfirmor = getEmailAddress(confirmorList)




    for itemMail in emailListCheckMan[threeDay.day - 1]:
       receivers.append(itemMail)

    for itemMail in emailListConfirmor[threeDay.day - 1]:
        receivers.append(itemMail)

    #print receivers

# 邮件正文内容
    if deviceName:
        message = MIMEText(threeDay.strftime('%Y-%m-%d') + u'请保养'+deviceName, 'plain', 'utf-8')
    else:
        message = MIMEText(threeDay.strftime('%Y-%m-%d') + '请保养设备','plain','utf-8')

    message['From'] = Header("AutoMail")
    message['To'] = Header("CheckMan")
    subject = 'TPM'
    message['Subject'] = Header(subject, 'utf-8')
# 构造附件1，传送当前目录下的 test.txt 文件
#att1 = MIMEText(open('meal.xls', 'rb').read(), 'base64', 'utf-8')
#att1["Content-Type"] = 'application/octet-stream'
# 这里的filename可以任意写，写什么名字，邮件中显示什么名字
#att1["Content-Disposition"] = 'attachment; filename="餐厅刷卡记录.xls"'
#message.attach(att1)

    try:
        smtpObj = smtplib.SMTP()
        smtpObj.connect(mail_host, 25)  # 25 为 SMTP 端口号
        smtpObj.login(mail_user, mail_pass)
        smtpObj.sendmail(sender, receivers, message.as_string())
        print "邮件发送成功"
    except smtplib.SMTPException:
        print "Error: 无法发送邮件"
    except Exception:
        pass
